﻿// =================================================================== 
// 项目说明
//====================================================================
// 
// 文件： SYS_DEPT.cs
// 项目名称：

// 创建时间：2013/3/19
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CommonLib.Model;
using CommonLib.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  .
    /// </summary>
    public partial class SYS_DEPTDAL : ISYS_DEPTDAL
    {
    //此处写查询数据的sql语句，



        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取SYS_DEPT表



        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
		//注意：所写sql语句执行结果的字段必须是唯一的



		private const string QueryDataString = "";

        /// </summary>
        /// <param name="sWhere">条件字符串</param>
        /// <param name="sOder">排序字段</param>
        /// <returns>DataSet</returns>
        public DataSet GetListSub(string sWhere, string sOder)
        {
            //string sqlStr = "select SQL_A.*,(select a.dept_name from Sys_Dept  a where a.dept_id=SQL_A.parent_id)  Parent_name,(select count(1) from Sys_Dept b where b.parent_id =SQL_A.dept_id) leaf  from " + TableString;
            //string sqlStr = "Select a.* From (select a.* ,1 as LEAF ,b.dept_name as PARENT_NAME,c.dept_name as SUPPLY_NAME from sys_dept a,sys_dept b,sys_dept c  "
            //            + " where b.dept_id=a.parent_id and b.for_supply=c.dept_id  union "
            //            + " select a.*,1 as leaf,'' as parent_name,'' as supply_name from sys_dept a where dept_id='00' ) a";
            string sqlStr = "select a.* From (select a.* ,1 as LEAF ,b.dept_name as PARENT_NAME from sys_dept a,sys_dept b "
                        + " where b.dept_id=a.parent_id   union "
                        + " select a.*,1 as leaf,'' as parent_name from sys_dept a where dept_id='00' ) a";
            if (sWhere.Length > 0)
            {
                sqlStr += " where " + sWhere;         
            }

            if (sOder.Length > 0)
                sqlStr += " order by " + sOder;
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr);
        }

        public string GetNewDeptId(string parentId)
        {
            //string sSQL = "select Replicate('0',case when (len('" + parentId + "__')-len(cast( MAX(DEPT_id)+1 as varchar(10)))) <0 then 0 else (len('" + parentId + "__')-len(cast( MAX(DEPT_id)+1 as varchar(10)))) end ) + cast( MAX(DEPT_id)+1 as varchar(10)) FROM SYS_DEPT WHERE lTRIM(DEPT_id) LIKE '" + parentId + "__'";
            string sSQL = "select '" + parentId + "' + substring(snewid,len('" + parentId + "')+1,2) "
                + " from ("
                + " select Replicate('0',case when (len('" + parentId + "__')-len(cast( MAX(DEPT_id)+1 as varchar(10)))) <0 then 0 else (len('" + parentId + "__')-len(cast( MAX(DEPT_id)+1 as varchar(10)))) end ) + cast( MAX(DEPT_id)+1 as varchar(10)) snewid "
                + " FROM SYS_DEPT WHERE lTRIM(DEPT_id) LIKE '" + parentId + "__'"
                + " )t";
            return SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.Text, sSQL).ToString();
        }
	
	}
}

